﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;
using bdxyit.com.RecruitmentAppSys.Models;

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class ProfessionalTechnicalInfoDAL : IProfessionalTechnicalInfoDAL
    {
        private const string SQL_SELECT_PROFESSIONALTECHNICALINFO = " select  ID,StartTime,EndTime,ProTecID,ProTecNameID,ProTecLevelID,ProTecNum,UserID from ProfessionalTechnicalInfos ";
        private const string SQL_SELECT_PROFESSIONALTECHNICALINFO_BY_USERID = " select  top 1 ID,StartTime,EndTime,ProTecID,ProTecNameID,ProTecLevelID,ProTecNum,UserID from ProfessionalTechnicalInfos "
                                                                            + "  where  UserID=@UserID";
        private const string SQL_DELETE_PROFESSIONALTECHNICALINFO_BY_USERID = " delete from ProfessionalTechnicalInfos "
                                                                            + "  where  UserID=@UserID";
        private const string SQL_UPDATE_PROFESSIONALTECHNICALINFO_BY_USERID = " update ProfessionalTechnicalInfos set "
                                                                            + " ID=@ID,"
                                                                            + " StartTime=@StartTime,"
                                                                            + " EndTime=@EndTime,"
                                                                            + " ProTecID=@ProTecID,"
                                                                            + " k=@ProTecNameID,"
                                                                            + " ProTecLevelID=@ProTecLevelID,"
                                                                            + " ProTecNum=@ProTecNum,"
                                                                            + " UserID=@UserID"
                                                                            + "  where  UserID=@UserID";
        private const string SQL_INSERT_PROFESSIONALTECHNICALINFO = " insert into ProfessionalTechnicalInfos("
                                                                + " ID,StartTime,EndTime,ProTecID,ProTecNameID,ProTecLevelID,ProTecNum,UserID)"
                                                                + "  values ("
                                                                + " @ID,@StartTime,@EndTime,@ProTecID,@ProTecNameID,@ProTecLevelID,@ProTecNum,@UserID)";
        private const string FIELD_ID = "@ID";
        private const string FIELD_StartTime = "@StartTime";
        private const string FIELD_EndTime = "@EndTime";
        private const string FIELD_ProTecID = "@ProTecID";
        private const string FIELD_ProTecNameID = "@ProTecNameID";
        private const string FIELD_ProTecLevelID = "@ProTecLevelID";
        private const string FIELD_ProTecNum = "@ProTecNum";
        private const string FIELD_UserID = "@UserID";

        public bool InsertProfessionalTechnicalInfo(ProfessionalTechnicalInfo cmodel)
        {

            bool flag = false;
            SqlParameter[] ProfessionalTechnicalInfoParms = GetProfessionalTechnicalInfoParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            ProfessionalTechnicalInfoParms[0].Value = cmodel.ID;
            ProfessionalTechnicalInfoParms[1].Value = cmodel.StartTime;
            ProfessionalTechnicalInfoParms[2].Value = cmodel.EndTime;
            ProfessionalTechnicalInfoParms[3].Value = cmodel.ProTecID;
            ProfessionalTechnicalInfoParms[4].Value = cmodel.ProTecNameID;
            ProfessionalTechnicalInfoParms[5].Value = cmodel.ProTecLevelID;
            ProfessionalTechnicalInfoParms[6].Value = cmodel.ProTecNum;
            ProfessionalTechnicalInfoParms[7].Value = cmodel.UserID;

            foreach (SqlParameter parm in ProfessionalTechnicalInfoParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_PROFESSIONALTECHNICALINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool DeleteProfessionalTechnicalInfo(string UserID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_PROFESSIONALTECHNICALINFO_BY_USERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool UpdateProfessionalTechnicalInfo(ProfessionalTechnicalInfo cmodel)
        {
            bool flag = false;
            SqlParameter[] ProfessionalTechnicalInfoParms = GetProfessionalTechnicalInfoParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            ProfessionalTechnicalInfoParms[0].Value = cmodel.ID;
            ProfessionalTechnicalInfoParms[1].Value = cmodel.StartTime;
            ProfessionalTechnicalInfoParms[2].Value = cmodel.EndTime;
            ProfessionalTechnicalInfoParms[3].Value = cmodel.ProTecID;
            ProfessionalTechnicalInfoParms[4].Value = cmodel.ProTecNameID;
            ProfessionalTechnicalInfoParms[5].Value = cmodel.ProTecLevelID;
            ProfessionalTechnicalInfoParms[6].Value = cmodel.ProTecNum;
            ProfessionalTechnicalInfoParms[7].Value = cmodel.UserID;

            foreach (SqlParameter parm in ProfessionalTechnicalInfoParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_PROFESSIONALTECHNICALINFO_BY_USERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public ProfessionalTechnicalInfo SelectProfessionalTechnicalInfoByUserID(string UserID)
        {
            ProfessionalTechnicalInfo cmodel = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_PROFESSIONALTECHNICALINFO_BY_USERID, parm))
            {
                if (rdr.Read())
                {
                    cmodel = new ProfessionalTechnicalInfo();
                    if (rdr.HasRows == true)
                    {
                        if (rdr["ID"] != null && rdr["ID"].ToString() != "")
                        {
                            cmodel.ID = int.Parse(rdr["ID"].ToString());
                        }
                        if (rdr["StartTime"] != null && rdr["StartTime"].ToString() != "")
                        {
                            cmodel.StartTime = DateTime.Parse(rdr["StartTime"].ToString());
                        }
                        if (rdr["EndTime"] != null && rdr["EndTime"].ToString() != "")
                        {
                            cmodel.EndTime = DateTime.Parse(rdr["EndTime"].ToString());
                        }
                        if (rdr["ProTecID"] != null && rdr["ProTecID"].ToString() != "")
                        {
                            cmodel.ProTecID = rdr["ProTecID"].ToString();
                        }
                        if (rdr["ProTecNameID"] != null && rdr["ProTecNameID"].ToString() != "")
                        {
                            cmodel.ProTecNameID = rdr["ProTecNameID"].ToString();
                        }
                        if (rdr["ProTecLevelID"] != null && rdr["ProTecLevelID"].ToString() != "")
                        {
                            cmodel.ProTecLevelID = rdr["ProTecLevelID"].ToString();
                        }
                        if (rdr["ProTecNum"] != null && rdr["ProTecNum"].ToString() != "")
                        {
                            cmodel.ProTecNum = rdr["ProTecNum"].ToString();
                        }
                        if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                        {
                            cmodel.UserID = rdr["UserID"].ToString();
                        }
                    }
                }
            }
            return cmodel;
        }
        public List<ProfessionalTechnicalInfo> SelectProfessionalTechnicalInfo()
        {
            List<ProfessionalTechnicalInfo> selectlist = new List<ProfessionalTechnicalInfo>();
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_PROFESSIONALTECHNICALINFO))
            {
                while (rdr.Read())
                {
                    ProfessionalTechnicalInfo cmodel = new ProfessionalTechnicalInfo();
                    //写添赋值语句
                    if (rdr["ID"] != null && rdr["ID"].ToString() != "")
                    {
                        cmodel.ID = int.Parse(rdr["ID"].ToString());
                    }
                    if (rdr["StartTime"] != null && rdr["StartTime"].ToString() != "")
                    {
                        cmodel.StartTime = DateTime.Parse(rdr["StartTime"].ToString());
                    }
                    if (rdr["EndTime"] != null && rdr["EndTime"].ToString() != "")
                    {
                        cmodel.EndTime = DateTime.Parse(rdr["EndTime"].ToString());
                    }
                    if (rdr["ProTecID"] != null && rdr["ProTecID"].ToString() != "")
                    {
                        cmodel.ProTecID = rdr["ProTecID"].ToString();
                    }
                    if (rdr["ProTecNameID"] != null && rdr["ProTecNameID"].ToString() != "")
                    {
                        cmodel.ProTecNameID = rdr["ProTecNameID"].ToString();
                    }
                    if (rdr["ProTecLevelID"] != null && rdr["ProTecLevelID"].ToString() != "")
                    {
                        cmodel.ProTecLevelID = rdr["ProTecLevelID"].ToString();
                    }
                    if (rdr["ProTecNum"] != null && rdr["ProTecNum"].ToString() != "")
                    {
                        cmodel.ProTecNum = rdr["ProTecNum"].ToString();
                    }
                    if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                    {
                        cmodel.UserID = rdr["UserID"].ToString();
                    }

                    selectlist.Add(cmodel);
                }
            }
            return selectlist;
        }
        private SqlParameter[] GetProfessionalTechnicalInfoParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter(FIELD_ID, SqlDbType.Int,4),
					new SqlParameter(FIELD_StartTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_EndTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_ProTecID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_ProTecNameID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_ProTecLevelID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_ProTecNum, SqlDbType.VarChar,100),
					new SqlParameter(FIELD_UserID, SqlDbType.VarChar,50)};
            return parms;
        }

    }
}